The auto-update statistics configuration updates statistics when the amount of row changes in the table achieves 20% of total rows.
However, for tables with too many rows, this threshold is too high. For example, a table with 1 million rows will only update statistics after 200 thousands changes. That’s too much and can affect query plans and query performance.
We can enable trace flag 2371 to change this behavior. Using this trace flag, threshold to trigger the auto-update statistics become dynamic. After 25k rows in the table, the amount of updates starts to decrease. A table with 1M rows needs around 3.2% of updates to trigger the auto-update statistics.
Even with this trace flag enabled, an important administrative task is identifying if the auto-update statistics is running often enough and, if not, schedule the update statistics using a SQL Agent job.
We can find when the last update statistics happened and how many row modifications happened since the last update statistics using sys.dm_db_stats_properties DMV with a few more joins.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT SCHEMA_NAME([o].[schema_id]) AS [schema], [o].[name] as [TABLE], [s].[name] AS Statistic, [sp].[last_updated], [sp].[rows], [sp].[rows_sampled], [sp].[modification_counter] AS [Modifications] FROM [sys].[stats] AS [s] inner join sys.stats_columns sc on s.stats_id=sc.stats_id and s.object_id=sc.object_id inner join sys.columns c on c.object_id=sc.object_id and c.column_id=sc.column_id inner join sys.objects o on s.object_id=o.object_id inner join sys.schemas sch on o.schema_id=sch.schema_id OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp] where sch.name<>'sys' and (sp.modification_counter > 1000 or (sp.modification_counter * 100) > sp.rows) -- removes rows with too few modifications order by [Percent] desc,last_updated desc -- the result in order of priority |
Let’s create a user defined function to make all the work easier. The function StatisticsModifications will retrieve information about all statistics in the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE FUNCTION StatisticsModifications () RETURNS TABLE AS RETURN ( SELECT SCHEMA_NAME([o].[schema_id]) AS [schema], [o].[name] as [TABLE], [s].[name] AS Statistic, [sp].[last_updated], [sp].[rows], [sp].[rows_sampled], [sp].[modification_counter] AS [Modifications] FROM [sys].[stats] AS [s] inner join sys.stats_columns sc on s.stats_id=sc.stats_id and s.object_id=sc.object_id inner join sys.columns c on c.object_id=sc.object_id and c.column_id=sc.column_id inner join sys.objects o on s.object_id=o.object_id inner join sys.schemas sch on o.schema_id=sch.schema_id OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp] where sch.name<>'sys' ) |
Now we can use this function with some filters to retrieve the most out dated statistics.
|
1 2 3 4 5 6 7 8 9 |
select [schema], [Table], [Statistic], [last_updated], [rows], [rows_sampled], [Modifications], (100 * modifications)/[rows] [Percent] from dbo.statisticsmodifications() /* removes rows with too few modifications */ where (modifications > 1000 or (modifications * 10) > [rows]) order by [Percent] desc,last_updated desc |
Using the result of the above query you can identify if the update statistics are running in regular intervals or if you need to schedule the update statistics.
Load comments